<?php
class Idea_Model extends CI_Model {

	function addIdea($data) {

		/*
		 * This function save idea's data into database
		 */
		$success = $this -> db -> insert('idea', $data);

		if ($success != 1) {
			//no idea is inserted
			return FALSE;
		} else {
			//get the inserted idea
			$ideas = $this -> db -> get_where('idea', array('idea_title' => $data['idea_title'])) -> result();
			return $ideas[0];
		}
	}

	function addIdeaTags($ideaId, $tagData) {
		/*
		 * This function save all tags of the idea to the database
		 */
		foreach ($tagData as $tag) {
			$trimmedTag = trim($tag);

			//search db for existing unique tag
			$tags = $this -> db -> get_where('tag', array('tag_name' => $trimmedTag)) -> result();

			$matched = count($tags);
			//if not found -> add tag to db, return tag_id of newly inserted tag
			if ($matched == 0) {
				$arrTag = array('tag_name' => $trimmedTag);
				$this -> db -> insert('tag', $arrTag);

				//find the newly inserted tag id
				$tags = $this -> db -> get_where('tag', array('tag_name' => $trimmedTag)) -> result();
			}

			//return tag_id

			$tag_id = $tags[0] -> tag_id;

			//assign the values to the joint table
			$ideaTagData = array('tag_id' => $tag_id, 'idea_id' => $ideaId);
			$this -> db -> insert('idea_tag', $ideaTagData);

		}
	}

	function removeIdeaTags($ideaId) {
		/*
		 * This function remove all idea's tags in the database
		 */
		$this -> db -> where('idea_id', $ideaId);
		$this -> db -> delete('idea_tag');
	}

	function getIdeasOfUser($username){
		//get 3 ideas of the username
		$this->db->order_by("idea_viewcount", "desc");
		
		return $this->db->get_where('idea', array('user_user_username' => $username), 3, 0)->result();
	}
	
	function getAllUserIdeas($username){
		//get all ideas of the username
		$this->db->order_by("idea_date", "desc");
		
		return $this->db->get_where('idea', array('user_user_username' => $username))->result();
	}
	
	function getIdeaById($id) {
		/*
		 * This function get idea's content from the database based on id
		 */
		$viewIdeas = $this -> db -> get_where('idea', array('idea_id' => $id)) -> result();
		if (!$viewIdeas) {
			//error 404
			show_404(base_url());
		} else {
			//return idea
			return $viewIdeas[0];
		}
	}

	function getIdeaByCategory($categoryName) {
		/*
		 * This function returns all ideas based on the category
		 */
		return $this -> db -> get_where('idea', array('idea_category' => $categoryName)) -> result();
	}

	function getIdeaUnderCategory($parentCategory) {
		/*
		 * This function returns all ideas based on the parent category of the idea
		 */
		switch ($parentCategory) {
			case 'technologies' :
				$this -> db -> where('idea_category', 'phones');
				$this -> db -> or_where('idea_category', 'computers');
				$this -> db -> or_where('idea_category', 'tablets');
				return $this -> db -> get('idea') -> result();
				break;

			case 'design' :
				$this -> db -> where('idea_category', 'fashion');
				$this -> db -> or_where('idea_category', 'jewelleries');
				return $this -> db -> get('idea') -> result();
				break;

			case 'home' :
				$this -> db -> where('idea_category', 'furniture');
				$this -> db -> or_where('idea_category', 'appliances');
				$this -> db -> or_where('idea_category', 'stationaries');
				return $this -> db -> get('idea') -> result();
				break;

			case 'application' :
				$this -> db -> where('idea_category', 'windows');
				$this -> db -> or_where('idea_category', 'osx');
				$this -> db -> or_where('idea_category', 'blackberry');
				$this -> db -> or_where('idea_category', 'android');
				$this -> db -> or_where('idea_category', 'ios');
				$this -> db -> or_where('idea_category', 'other platform');
				return $this -> db -> get('idea') -> result();
				break;

			case 'uncategorised' :
				$this -> db -> where('idea_category', 'uncategorised');
				return $this -> db -> get('idea') -> result();
				break;

			case 'all' :
				$this -> db -> order_by('idea_viewcount');
				return $this -> db -> get('idea') -> result();
				break;

			default :
				break;
		}
	}

	function updateViewCount($ideaToUpdate) {

		//update viewcount +1
		$updateData = array('idea_viewcount' => $ideaToUpdate -> idea_viewcount + 1);

		$this -> db -> update('idea', $updateData, array('idea_id' => $ideaToUpdate -> idea_id));
	}
	
	function checkBump($idea_id, $username) {
		//function used to chechk whether user had bumped this idea before
		$rows = $this -> db -> get_where('bump', array('idea_idea_id' => $idea_id, 'user_user_username' => $username)) -> result();
		return count($rows);

	}
	
	function getMainIdea(){
		//get the longest premium idea
		$this->db->limit(1);
		$this->db->where('idea_ispremium', 1);
		$this->db->order_by('idea_premium_limit' , 'desc');
		
		$result = (array)$this->db->get('idea')->result();
		
		return $result[0];
	}

/*
	 * This function is called by the mainpage controller to retrieve the premium ideas to be displayed at the mainpage
	 * Returns array of ideas
	 */ 	
	function getPremiums(){
		//get the other premium ideas
		$this->db->limit(3, 1);
		$this->db->where('idea_ispremium', 1);
		$this->db->order_by('idea_premium_limit' , 'desc');
		
		return $this->db->get('idea')->result();
	}
	
	/*
	 * This function is called by the mainpage controller to retrieve the 3 most highest views to be displayed at the mainpage
	 * Returns array of ideas
	 */ 
	function getMostViewed(){
		//get ideas with most views
		$this->db->limit(3);
		$this->db->order_by("idea_viewcount", "desc");
		
		return $this->db->get("idea")->result();
	}
	
	/*
	 * This function is called by the mainpage controller to retrieve the 3 most highest bumps to be displayed at the mainpage
	 * Returns array of ideas
	 */ 
	function getMostBump(){
		//get ideas with most bumps
		$this->db->limit(3);
		$this->db->order_by("idea_bumps", "desc");
		
		return $this->db->get("idea")->result();
	}
	
	function getIdeaByInterest($interests){
		//get ideas based on interests by user
		
		$mainResult = array();
		
		//loop through all interest
		foreach($interests as $category){

			$category = strtolower($category);
			$result = (array)$this->getIdeaUnderCategory($category);
			
			//append result to main result array
			foreach ($result as $idea){
				$mainResult[] = $idea;
			}
			
		}
		
		//function to sort by the number of views
		function view_compare($a, $b) {
			return $a->idea_viewcount - $b->idea_viewcount;
		}
		
		usort($mainResult, 'view_compare');
		
		return array_slice($mainResult, 0, 3);
	}

/*
 *This function called by the controller to insert the bump/vote data to the database take idea_id and user_username as parameter
 *  
 */ 
	function addBump($idea_id, $username) {
		//save bump data to database
		
		//check whether user had bumped this idea before
		$success = $this -> checkBump($idea_id, $username);
		
		if ($success != 0) {
			//user had bumped before
			return 0;
		} else {
			//insert bump data to database
			$data = array('idea_idea_id' => $idea_id, 'user_user_username' => $username);
			$this -> db -> insert('bump', $data);
			$idea = $this -> getIdeaById($idea_id);
			$updateData = array('idea_bumps' => $idea -> idea_bumps + 1);

			return $this -> db -> update('idea', $updateData, array('idea_id' => $idea_id));

		}
	}
	
/*
 *This function take all of the related idea from a particular idea, take idea_id as the parameter 
 */
	function getRelatedIdeas($id) {

		//get all the tags from the idea

		$tagsOfIdea = $this -> getIdeaTags($id);
		$matched = count($tagsOfIdea);
		//get all ideas' id related to the tag
		//SELECT DISTINCT idea_id FROM idea JOIN idea_tag ON idea.idea_id = idea_tag.idea_id WHERE tag_id ORDER BY popularity
		if ($matched > 0) {
			$this -> db -> select('idea.idea_id, idea_title, idea_overview, idea_image, idea_date, user_user_username, idea_bumps, idea_overview');
			$this -> db -> distinct();
			$this -> db -> from('idea');
			$this -> db -> join('idea_tag', 'idea_tag.idea_id = idea.idea_id', 'inner');
			$this -> db -> where_not_in('idea.idea_id', array($id));

			//manually create grouped or query
			$orQuery;
			for ($i = 0; $i < count($tagsOfIdea); $i++) {
				if ($i == 0) {
					$orQuery = "`tag_id` = '" . $tagsOfIdea[$i] -> tag_id . "'";
				} else {
					$orQuery .= " OR `tag_id` = '" . $tagsOfIdea[$i] -> tag_id . "'";
				}
			}

			if (isset($orQuery)) {
				//if orQuery is not null || idea has tags
				$groupedQuery = "(" . $orQuery . ")";

				$this -> db -> where($groupedQuery);

			}

			$this -> db -> order_by("idea_viewcount", "DESC");
			$this -> db -> limit(3, 0);
			$relatedIdeas = (array)$this -> db -> get() -> result();

			//return all ids
			return $relatedIdeas;
		}

	}

	function getIdeaTags($id) {
		/*
		 * This function gets all tag information from database based on the idea id
		 */
		$this -> db -> select('*');
		$this -> db -> from('idea_tag');
		$this -> db -> join('tag', 'idea_tag.tag_id = tag.tag_id', 'inner');
		$this -> db -> where('idea_id', $id);
		
		$tagsOfIdea = $this -> db -> get() -> result();
		
		return $tagsOfIdea;
	}

	function getTagName($tagId) {
		/*
		 * This function return the name of the tag based on the id of the tag
		 */
		$namesOfTags = $this -> db -> get_where('tag', array('tag_id' => $tagId)) -> result();
		return $namesOfTags[0];
	}

	function updateIdea($id, $data) {
		/*
		 * This function update the current information about an idea in the database based on the id
		 */
		$this -> db -> where('idea_id', $id);
		return $this -> db -> update('idea', $data);
	}

	function deleteIdea($id) {
		/*
		 * This function delete the idea based on the id
		 */
		$this -> db -> where('idea_id', $id);
		return $this -> db -> delete('idea');
	}

/*
 *Search idea function where controller sent the string query, method of sorting, ordering method, and category for filtering 
 */
	function search($searchString, $sortBy, $orderMethod, $category) {
		/*
		 * This function search the database based on the searchString
		 */
		$mainResult = array();
		$resultIds = array();

		if ($sortBy == 'Relevance') {
			//search for exact match
			$this -> db -> where('idea_title', $searchString);
			
			
			$results = $this -> db -> get('idea') -> result();
			
			foreach ($results as $result) {
				$mainResult[] = $result;
				$resultIds[] = $result -> idea_id;
			}//end of foreach
			//search with like for title and content
			
			$likeQuery = "(
					idea_title LIKE '%" . $searchString . "%' OR
					idea_overview LIKE '%" . $searchString . "%' OR
					idea_problem LIKE '%" . $searchString . "%' OR
					idea_solution LIKE '%" . $searchString . "%' OR
					idea_category LIKE '%". $searchString . "%'
					)";
			$this -> db -> where($likeQuery);
			
			//category filter
			if($category != 'uncategorised')
			{
				//echo($category);
				$this->db->where('idea_category', $category);
			}
			
			if (count($resultIds) > 0) {
				$this -> db -> where_not_in('idea_id', $resultIds);
			}

			$results = $this -> db -> get('idea') -> result();
			foreach ($results as $result) {
				$mainResult[] = $result;
				$resultIds[] = $result -> idea_id;
			}

			//search for tag
			$this -> db -> from('idea');
			$this -> db -> join('idea_tag', 'idea.idea_id = idea_tag.idea_id');
			$this -> db -> join('tag', 'idea_tag.tag_id = tag.tag_id');
			$this -> db -> where('tag_name', $searchString);
			
			//category filter
			if($category != 'uncategorised')
			{
				//echo($category);
				$this->db->where('idea_category', $category);
			}
			
			if (count($resultIds) > 0) {
				$this -> db -> where_not_in('idea.idea_id', $resultIds);
			}
			
			
			$results = $this -> db -> get() -> result();
			foreach ($results as $result) {
				$mainResult[] = $result;
				$resultIds[] = $result -> idea_id;
			}
			//return final result
			return $mainResult;
		}//end if relevance
		
		
		else {
			
			
			//category filter
			if($category != 'uncategorised')
			{
				//echo($category);
				$this->db->where('idea_category', $category);
			}
			
			if (count($resultIds) > 0) {
				$this -> db -> where_not_in('idea.idea_id', $resultIds);
			}
			
			
			
				
			//search using order
			$likeQuery = "(
					idea_title LIKE '%" . $searchString . "%' OR
					idea_overview LIKE '%" . $searchString . "%' OR
					idea_problem LIKE '%" . $searchString . "%' OR
					idea_solution LIKE '%" . $searchString . "%' OR
					idea_category LIKE '%". $searchString . "%'
					)";
			$this -> db -> where($likeQuery);
			
			//category filter
			if($category != 'uncategorised')
			{
				//echo($category);
				$this->db->where('idea_category', $category);
			}
			if (count($resultIds) > 0) {
				$this -> db -> where_not_in('idea_id', $resultIds);
			}
			
			//sort based on user choice ascending
			if($orderMethod == 'asc'){
				$this->db->order_by($sortBy, 'asc');
			}
			
			//sort based on user choice descending
			if($orderMethod == 'desc'){
				$this->db->order_by($sortBy, 'desc');
			}
			
			
			$results = $this -> db -> get('idea') -> result();
			foreach ($results as $result) {
				$mainResult[] = $result;
				$resultIds[] = $result -> idea_id;
			}
			
			//search for tag
			$this -> db -> from('idea');
			$this -> db -> join('idea_tag', 'idea.idea_id = idea_tag.idea_id');
			$this -> db -> join('tag', 'idea_tag.tag_id = tag.tag_id');
			$this -> db -> where('tag_name', $searchString);
			
			//category filter
			if($category != 'uncategorised')
			{
				//echo($category);
				$this->db->where('idea_category', $category);
			}
			
			if (count($resultIds) > 0) {
				$this -> db -> where_not_in('idea.idea_id', $resultIds);
			}
			
			
			$results = $this -> db -> get() -> result();
			foreach ($results as $result) {
				$mainResult[] = $result;
				$resultIds[] = $result -> idea_id;
			}
			
			return $mainResult;
		}//end else

	}

	/*
	 * This function called by the upgrade premium controller to insert the data sent by the controller to the database
	 */
	function upgradePremium($id, $expiry) {
		$query = "UPDATE idea
					SET idea_ispremium = '1',
					idea_premium_limit ='" . $expiry . "'
					WHERE idea_id = '" . $id . "'";
		return $this -> db -> query($query);
	}

}
?>